package com.nari.heatshare.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.nari.coreframe.dao.hibernate3.GenericDaoImpl;
import com.nari.heatshare.model.IAssignMeterDataStatistics;
import com.nari.platform.date.CalendarUtils;
import com.nari.platform.model.PageCond;
import com.nari.platform.model.ResultWithPageCond;
import com.nari.platform.utils.StringUtils;
import com.nari.rescontroller.dao.IResourceAccessControlDao;
import com.nari.rescontroller.dao.ResourceAccessControlDaoImpl;

public class AssignMeterDataStatisticsDaoImpl extends GenericDaoImpl<IAssignMeterDataStatistics, String> 
		implements IAssignMeterDataStatisticsDao {
	private IResourceAccessControlDao iResourceAccessControlDao;
	//JDBC Template
	private JdbcTemplate jdbcTemplate;
	/**
	 * 分页查询分配表数据上传的任务批次统计
	 * 分配表数据上传的任务批次条件：集中器号、抄表日期
	 * @param concentratorNo 集中器号
	 * @param startDate 抄表日期起始日期
	 * @param endDate 抄表日期结束日期
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-25
	 */
	@SuppressWarnings("unchecked")
	@Deprecated
	public ResultWithPageCond<IAssignMeterDataStatistics> queryMeterDataStatisPage_back(String concentratorNo, 
			Date startDate, Date endDate, PageCond pageCond) throws Exception {
		Criteria listCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataStatistics.class);
		Criteria countCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataStatistics.class);
		//权限资源匹配
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResHql();
		if(!"".equals(authorityRes)) {
			List<Object> projectUIDList = this.runQueryHSqlObject(authorityRes);
			String formatProjectUIDList = StringUtils.formatIDsByList(projectUIDList);
			formatProjectUIDList = StringUtils.formatIDListWithSyntax(formatProjectUIDList, ",", "'");
			//这是一个直接与底层数据库耦合的查询，注意避免数模更改带来的系统问题
			listCriteria.add(Restrictions.sqlRestriction("{alias}.t_concentrator_no in(select lower(t_concentrator_no) from BASE_CONCENTRATOR_INFO where t_project_id in("+formatProjectUIDList+"))"));
			countCriteria.add(Restrictions.sqlRestriction("{alias}.t_concentrator_no in(select lower(t_concentrator_no) from BASE_CONCENTRATOR_INFO where t_project_id in("+formatProjectUIDList+"))"));
		}
		// 所属集中器号
		if (concentratorNo != null && !"".equals(concentratorNo.trim())) {
			listCriteria.add(Restrictions.ilike("concentratorNo", concentratorNo.trim().toLowerCase(), MatchMode.ANYWHERE));
			countCriteria.add(Restrictions.ilike("concentratorNo", concentratorNo.trim().toLowerCase(), MatchMode.ANYWHERE));
		}
		// 抄表时间
		if (startDate != null && endDate == null) {
			Date startRecordTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			listCriteria.add(Restrictions.ge("recordTime", startRecordTime));
			countCriteria.add(Restrictions.ge("recordTime", startRecordTime));
		} else if (startDate == null && endDate != null) {
			Date endRecordTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.le("recordTime", endRecordTime));
			countCriteria.add(Restrictions.le("recordTime", endRecordTime));
		} else if (startDate != null && endDate != null) {
			Date startRecordTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			Date endRecordTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.between("recordTime", startRecordTime, endRecordTime));
			countCriteria.add(Restrictions.between("recordTime", startRecordTime, endRecordTime));
		}
		// 统计结果集个数
		int total = ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
		// 结果集排序
		listCriteria.addOrder(Order.desc("updateTime")).addOrder(Order.desc("recordTime")).addOrder(Order.asc("concentratorNo"));
		// 结果集分页
		listCriteria.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		// 构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<IAssignMeterDataStatistics> meterDataStatisPage = new ResultWithPageCond<IAssignMeterDataStatistics>();
		meterDataStatisPage.setList(listCriteria.list());
		meterDataStatisPage.setPageCond(pageCond);
		return meterDataStatisPage;
	}
	
	
	/* 
	 * 使用JDBC 来查询定点上传
	 * @see com.nari.heatshare.dao.IAssignMeterDataStatisticsDao#queryMeterDataStatisPage(java.lang.String, java.util.Date, java.util.Date, com.nari.platform.model.PageCond)
	 */
	public ResultWithPageCond<IAssignMeterDataStatistics> queryMeterDataStatisPage(String concentratorNo, 
			Date startDate, Date endDate, PageCond pageCond) throws Exception {
		ResultWithPageCond<IAssignMeterDataStatistics> result = new ResultWithPageCond<>();
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResHql();
		//如果是管理员或者是超级销售
		boolean isAdmin = false;
		ResourceAccessControlDaoImpl dao = (ResourceAccessControlDaoImpl) iResourceAccessControlDao;
		String sysadmin = dao.getSysadmin();
		if("sysAdministrator".equals(sysadmin)) {
			isAdmin = true;
		}
		String innerSql = "JOIN ( SELECT t_concentrator_no, t_building_id  FROM	 base_concentrator_info	WHERE 1 = 1 ";
		if(org.apache.commons.lang3.StringUtils.isNotBlank(concentratorNo)) {
			concentratorNo = concentratorNo.trim();
			innerSql  = innerSql + " AND t_concentrator_no = '"+concentratorNo+"' ";
		}
		if(!"".equals(authorityRes)) {
			List<Object> projectUIDList = this.runQueryHSqlObject(authorityRes);
			String formatProjectUIDList = StringUtils.formatIDsByList(projectUIDList);
			formatProjectUIDList = StringUtils.formatIDListWithSyntax(formatProjectUIDList, ",", "'");
			//这是一个直接与底层数据库耦合的查询，注意避免数模更改带来的系统问题
			 innerSql =  innerSql + "	AND t_project_id IN ("+formatProjectUIDList+")";
		}
		innerSql = innerSql + ") AS info ON info.t_concentrator_no = statis.t_concentrator_no ";
		if(endDate==null) endDate = new Date();
		if(startDate == null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endDate);
			calendar.add(Calendar.DAY_OF_YEAR, -7);
			startDate = calendar.getTime();
		}
		String startString =	CalendarUtils.parseDateTimeToString( CalendarUtils.getMinCalBasedSrcCal(startDate));
		String endString =  CalendarUtils.parseDateTimeToString( CalendarUtils.getMaxCalBasedSrcCal(endDate));
		String outerSql = "SELECT	statis.ID AS id,	statis.t_concentrator_no AS concentratorNo,	statis.t_assign_meter_count AS assignMeterCount,	statis.t_upload_count AS uploadCount,	statis.t_save_success_count AS saveSuccessCount,	statis.t_upload_percent AS uploadPercent ,	statis.t_record_time AS  recordTime,	statis.t_update_time AS  updateTime,	statis.t_read_time AS readTime FROM "
    +	"p_assign_meter_data_statistics AS statis ";
		String sqlEnd = "  WHERE statis.t_record_time BETWEEN '"+startString+"' AND '"+endString+"' ";
		String counterSql = "SELECT COUNT(1) FROM p_assign_meter_data_statistics AS statis ";
		//管理员取消innersql
		if(isAdmin) {
			innerSql = "";
		}
		if(org.apache.commons.lang3.StringUtils.isNotBlank(concentratorNo)) {
			counterSql = counterSql +sqlEnd+" AND statis.t_concentrator_no = '"+concentratorNo.trim()+"' ";
		}else {
			counterSql = counterSql +innerSql+sqlEnd;
		}
		String  orderBy = "ORDER BY statis.t_update_time DESC,t_record_time DESC,statis.t_concentrator_no DESC ";
		String  limit = "limit "+pageCond.getStart()+","+pageCond.getLimit();
		if(org.apache.commons.lang3.StringUtils.isNotBlank(concentratorNo)) {
			outerSql = outerSql  +sqlEnd+" AND statis.t_concentrator_no = '"+concentratorNo.trim()+"'   "+orderBy+limit;
		}else {
			outerSql = outerSql + innerSql+sqlEnd+orderBy +limit;
		}
		int count = jdbcTemplate.queryForInt(counterSql);
		pageCond.setTotal(count);
		List list = jdbcTemplate.query(outerSql, new BeanPropertyRowMapper(IAssignMeterDataStatistics.class));
		result.setPageCond(pageCond);
		result.setList(list);
		return result;
	}
	
	/**
	 * 根据上传任务标识(集中器号、抄表日期)统计上传任务的个数
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-11-13
	 */
	public int countStatisNumByUploadMission(String concentratorNo, 
			Date recordTime) throws Exception {
		String hsql = "select count(*) from IAssignMeterDataStatistics statis " +
				"where statis.concentratorNo='"+concentratorNo+"' " +
				"and statis.recordTime between :startTime and :endTime";
		Date startRecordTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date endRecordTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", startRecordTime).setTimestamp("endTime", endRecordTime);
		return ((Long)query.uniqueResult()).intValue();
	}

	/**
	 * 根据上传任务标识(集中器号、抄表日期)查询分配表数据统计结果
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-11-13
	 */
	@SuppressWarnings("unchecked")
	public IAssignMeterDataStatistics queryStatisByUploadMission(String concentratorNo, 
			Date recordTime) throws Exception {
		String hsql = "from IAssignMeterDataStatistics statis " +
				"where statis.concentratorNo='"+concentratorNo+"' " +
				"and statis.recordTime between :startTime and :endTime";
		Date startRecordTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date endRecordTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startTime", startRecordTime).setTimestamp("endTime", endRecordTime);
		List<IAssignMeterDataStatistics> statisList = query.list();
		if(statisList!=null && statisList.size()>0) {
			return statisList.get(0);
		}
		return null;
	}

	/**
	 * @return the iResourceAccessControlDao
	 */
	public IResourceAccessControlDao getiResourceAccessControlDao() {
		return iResourceAccessControlDao;
	}

	/**
	 * @param iResourceAccessControlDao the iResourceAccessControlDao to set
	 */
	public void setiResourceAccessControlDao(
			IResourceAccessControlDao iResourceAccessControlDao) {
		this.iResourceAccessControlDao = iResourceAccessControlDao;
	}


	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}


	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
}
